<?php
	require_once("Service.php");
	require_once("Database.php");
	require_once("vo/UserVO.php");
	require_once("vo/GroupVO.php");
	require_once("vo/ModuleVO.php");
	require_once("vo/SponsorVO.php");
	require_once("utils/class.phpmailer.php");
	
	/**
	 * Class which handles all administrator related queries.
	 */
	class AdministratorService extends Service {
		
		var $conn;
		
		/** The constructor of this object. */
		function __construct() {
			
			parent::__construct();
			$this->conn = new Database();
		}
		
		/**
		 * Finds the site usage statistics.
		 * @param $sessionID The sessionID of the user calling this function.
		 * @return An object containing the usage statistics.
		 */
		function getStatistics($sessionID) {
			
			$this->validateSession($sessionID);
			
			$currentYear = date("Y");
			$currentMonth = date("m");
			
			$monthStart = $currentYear . "-" . $currentMonth . "-01";
			$monthEnd = $currentYear . "-" . ($currentMonth + 1) . "-01";
			
			$result = array();
			
			// get the daily logins made on the system for the current month
			$query = Database::buildQuery("
				SELECT
					DAYOFMONTH(loginDate) AS day,
					COUNT(loginDate) AS noLogins
				FROM Logins
				WHERE loginDate >= ?
       			AND loginDate < ?
				GROUP BY day
				ORDER BY day ASC
			", $monthStart, $monthEnd);
			$qryLogins = $this->conn->execute($query);
			
			$logins = array();
			while($recLogins = mysql_fetch_assoc($qryLogins)) {
				$logins[] = $recLogins;
			}
			$result["logins"] = $logins;
			
			// get the 5 most active users for the last month
			$query = Database::buildQuery("
				SELECT
					Users.userUID,
					Users.firstName,
					Users.lastName,
					COUNT(Logins.userUID) AS noLogins
				FROM Logins
				INNER JOIN Users
					ON Users.userUID = Logins.userUID
				WHERE Logins.loginDate >= ?
       			AND Logins.loginDate < ?
				GROUP BY
					Users.userUID,
					Users.firstName,
					Users.lastName
				ORDER BY noLogins DESC
				LIMIT 5
			", $monthStart, $monthEnd);
			$qryLogins = $this->conn->execute($query);
			
			$users = array();
			while($recUsers = mysql_fetch_assoc($qryLogins)) {
				$users[] = $recUsers;
			}
			$result["users"] = $users;
			
			return $result;
		}
		
		/**
		 * Finds a list of all the users on the system.
		 * @param $sessionID The sessionID of the user calling this function.
		 * @return An array containing all of the users.
		 */
		function getUserList($sessionID) {
			
			$this->validateSession($sessionID);
			
			// get a list of all the users on the system
			$qryUsers = $this->conn->execute("
				SELECT
					userUID,
					firstName,
					lastName,
					email,
					phoneNumber
				FROM Users
				WHERE isArchived = 0
				ORDER BY firstName ASC, lastName ASC
			");
			
			$users = array();
			while($recUsers = mysql_fetch_assoc($qryUsers)) {
				$users[] = UserVO::mapObject($recUsers);
			}
			
			return $users;
		}
		
		/**
		 * Sends a SMS message to a list of users.
		 * @param $sessionID The sessionID of the user calling this function.
		 * @param $phoneNumbers The phone numbers to sent the message to.
		 * @param $apiID The Click-a-tell account API ID to use when sending the message.
		 * @param $userName The api ID user name.
		 * @param $password The api ID password.
		 * @param $message The message to be sent.
		 */
		function smsUsers($sessionID, $phoneNumbers, $apiID, $userName, $password, $message) {
			
			$this->validateSession($sessionID);
			
			$message = urlencode($message);
			
			for($i = 0; $i < sizeof($phoneNumbers); $i++) {
				file("http://api.clickatell.com/http/sendmsg?user=$userName&password=$password&api_id=$apiID&to=$phoneNumbers[$i]&text=$message");
			}
		}
		
		/**
		 * Sends an email message to a list of users.
		 * @param $sessionID The sessionID of the user calling this function.
		 * @param $emailAddresses The email addresses to sent the message to.
		 * @param $fromAddress The address to send the message from.
		 * @param $heading The heading of the email.
		 * @param $message The message to be sent.
		 */
		function emailUsers($sessionID, $emailAddresses, $fromAddress, $heading, $message) {
			
			$this->validateSession($sessionID);
			
			try {
				
				$mail = new PHPMailer(true);
				$mail->IsSendmail();
				
				for($i = 0; $i < sizeof($emailAddresses); $i++) {
					$mail->AddAddress($emailAddresses[$i]);
				}
				
				$mail->SetFrom($fromAddress);
				$mail->Subject = $heading;
				$mail->MsgHTML($message); //$message = eregi_replace("[\]",'',$message);
				$mail->IsHTML(true);
				$mail->Send();
				
			} catch (phpmailerException $e) {
				
				//echo $e->errorMessage(); // PHPMailer library error
				//Service::logOutput($e->errorMessage());
				
			} catch (Exception $e) {
				
				//echo $e->getMessage(); // Any other unknown error
				//Service::logOutput($e->getMessage());
			}
		}
		
		/**
		 * Attempts to add a new user on the system.
		 * @param $firstName The first name of the user.
		 * @param $lastName The last name of the user.
		 * @param $email The email address of the user.
		 * @param $password The password of the user.
		 * @param $phoneNumber The phone number of the user.
		 * @param $description A short biographic description of the user.
		 * @param $gender The gender of the user.
		 * @param $dateOfBirth The birth date of the user.
		 * @param $location The country/city where the user is from.
		 * @param $occupation The job title of the user.
		 * @param $institution The company the user works for.
		 * @return The session details of the user, otherwise null.
		 */
		function addUser(
			$sessionID,
			$firstName,
			$lastName,
			$email,
			$password,
			$phoneNumber,
			$description,
			$gender,
			$dateOfBirth,
			$location,
			$occupation,
			$institution) {
			
			$this->validateSession($sessionID);
			
			// check if the email address is unique
			$query = Database::buildQuery("
				SELECT userUID
				FROM Users
				WHERE email = ?
			", $email);
			$qryUserCheck = $this->conn->execute($query);
			
			if(mysql_num_rows($qryUserCheck) == 0) {
				
				$userUID = $this->conn->generateUID(); 
				
				// save the new user
				$query = Database::buildQuery("
					INSERT INTO Users(
						userUID,
						firstName,
						lastName,
						email,
						password,
						phoneNumber,
						description,
						gender,
						dateOfBirth,
						location,
						occupation,
						institution,
						avatarUID,
						themeUID
					) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
				", $userUID, $firstName, $lastName, $email, md5($password), $phoneNumber, $description, $gender, $dateOfBirth, $location, $occupation, $institution, "2", "9");
				$this->conn->execute($query);
				
				return $userUID;
				
			} else {
				
				return null;
			}
		}
		
		/**
		 * Saves changes made to a users details.
		 * @param $sessionID The sessionID of the user calling this function.
		 * @param $userUID The user who's details to edit.
		 * @param $firstName The first name of the user.
		 * @param $lastName The last name of the user.
		 * @param $email The email address of the user.
		 * @param $password The password of the user.
		 * @param $phoneNumber The phone number of the user.
		 * @param $description A short biographic description of the user.
		 * @param $gender The gender of the user.
		 * @param $dateOfBirth The birth date of the user.
		 * @param $location The country/city where the user is from.
		 * @param $occupation The job title of the user.
		 * @param $institution The company the user works for.
		 * @return True if successful, otherwise false.
		 */
		function editUser(
			$sessionID,
			$userUID,
			$firstName,
			$lastName,
			$email,
			$password,
			$phoneNumber,
			$description,
			$gender,
			$dateOfBirth,
			$location,
			$occupation,
			$institution) {
			
			$this->validateSession($sessionID);
			
			// check if the email address is unique
			$query = Database::buildQuery("
				SELECT userUID
				FROM Users
				WHERE email = ?
				AND userUID <> ?
			", $email, $userUID);
			$qryUserCheck = $this->conn->execute($query);
			
			if(mysql_num_rows($qryUserCheck) == 0) {
				
				// save changes to the user's details
				$query = Database::buildQuery("
					UPDATE Users
					SET
						firstName = ?,
						lastName = ?,
						email = ?,
						phoneNumber = ?,
						description = ?,
						gender = ?,
						dateOfBirth = ?,
						location = ?,
						occupation = ?,
						institution = ?
					WHERE userUID = ?
				", $firstName, $lastName, $email, $phoneNumber, $description, $gender, $dateOfBirth, $location, $occupation, $institution, $userUID);
				$this->conn->execute($query);
				
				if($password != "") {
					
					// save the user's password
					$query = Database::buildQuery("
						UPDATE Users
						SET password = ?
						WHERE userUID = ?
					", md5($password), $userUID);
					$this->conn->execute($query);
				}
				
				return true;
				
			} else {
				
				return false;
			}
		}
		
		/**
		 * Archives a system user.
		 * @param $sessionID The sessionID of the user calling this function.
		 * @param $userUID The user to archive.
		 * @return The userUID of the deleted user.
		 */
		function deleteUser($sessionID, $userUID) {
			
			$this->validateSession($sessionID);
			
			// delete the user
			$query = Database::buildQuery("
				UPDATE Users
				SET
					isArchived = 1,
					status = ?
				WHERE userUID = ?
			", "This user has been deleted", $userUID);
			$this->conn->execute($query);
			
			return $userUID;
		}
		
		/**
		 * Finds a list of all the groups on the system.
		 * @param $sessionID The sessionID of the user calling this function.
		 * @return An array containing all of the groups.
		 */
		function getGroupList($sessionID) {
			
			$this->validateSession($sessionID);
			
			// get a list of all the groups on the system
			$qryGroups = $this->conn->execute("
				SELECT
					groupUID,
					groupName,
					description,
					logoURL,
					chatURL
				FROM Groups
				WHERE isArchived = 0
				ORDER BY groupName ASC
			");
			
			$groups = array();
			while($recGroups = mysql_fetch_assoc($qryGroups)) {
				$groups[] = GroupVO::mapObject($recGroups);
			}
			
			return $groups;
		}
		
		/**
		 * Sends a SMS message to all of the members in a group.
		 * @param $sessionID The sessionID of the user calling this function.
		 * @param $groupUID The group to send the message to.
		 * @param $apiID The Click-a-tell account API ID to use when sending the message.
		 * @param $userName The api ID user name.
		 * @param $password The api ID password.
		 * @param $message The message to be sent.
		 */
		function smsGroup($sessionID, $groupUID, $apiID, $userName, $password, $message) {
			
			$this->validateSession($sessionID);
			
			// get the phone numbers of all the group members
			$query = Database::buildQuery("
				SELECT Users.phoneNumber
				FROM UserGroups
				INNER JOIN Users
					ON Users.userUID = UserGroups.userUID
				WHERE UserGroups.groupUID = ?
				AND UserGroups.isBanned = 0
				AND Users.phoneNumber IS NOT NULL
			", $groupUID);
			$qryNumbers = $this->conn->execute($query);
			
			$phoneNumbers = array();
			while($recNumbers = mysql_fetch_assoc($qryNumbers)) {
				$phoneNumbers[] = $recNumbers["phoneNumber"];
			}
			
			$this->smsUsers($sessionID, $phoneNumbers, $apiID, $userName, $password, $message);
		}
		
		/**
		 * Sends an email message to all of the members in a group.
		 * @param $sessionID The sessionID of the user calling this function.
		 * @param $groupUID The group to send the message to.
		 * @param $fromAddress The address to send the message from.
		 * @param $heading The heading of the email.
		 * @param $message The message to be sent.
		 */
		function emailGroup($sessionID, $groupUID, $fromAddress, $heading, $message) {
			
			$this->validateSession($sessionID);
			
			// get the email addresses of all the group members
			$query = Database::buildQuery("
				SELECT Users.email
				FROM UserGroups
				INNER JOIN Users
					ON Users.userUID = UserGroups.userUID
				WHERE UserGroups.groupUID = ?
				AND UserGroups.isBanned = 0
			", $groupUID);
			$qryEmails = $this->conn->execute($query);
			
			$emailAddresses = array();
			while($recEmails = mysql_fetch_assoc($qryEmails)) {
				$emailAddresses[] = $recEmails["email"];
			}
			
			$this->emailUsers($sessionID, $emailAddresses, $fromAddress, $heading, $message);
		}
		
		/**
		 * Adds a new group to the system.
		 * @param $sessionID The sessionID of the user calling this function.
		 * @param $userUID The user who is creating the group.
		 * @param $groupName The group name of the group.
		 * @param $description A description of the group.
		 * @param $logoURL The path to the group's logo.
		 * @param $chatURL The group's AFCS chat room url.
		 * @param $sponsors The sponsors of the group.
		 * @param $modules The modules the group uses.
		 * @return The groupUID of the new group.
		 */
		function addGroup(
			$sessionID,
			$userUID,
			$groupName,
			$description,
			$logoURL,
			$chatURL,
			$sponsors,
			$modules) {
			
			$this->validateSession($sessionID);
			
			$groupUID = $this->conn->generateUID();
			$queries = array();
			
			// save the group
			$queries[] = Database::buildQuery("
				INSERT INTO Groups (groupUID, groupName, description, logoURL, chatURL)
				VALUES (?, ?, ?, ?, ?)
			", $groupUID, $groupName, $description, $logoURL, $chatURL);
			
			// save the group manager
			$queries[] = Database::buildQuery("
				INSERT INTO UserGroups (groupUID, userUID, isManager)
				VALUES (?, ?, 1)
			", $groupUID, $userUID);
			
			// save the group sponsors
			for($i = 0; $i < sizeof($sponsors); $i++) {
				
				$sponsorUID = $this->conn->generateUID();
				$queries[] = Database::buildQuery("
					INSERT INTO GroupSponsors (sponsorUID, groupUID, sponsorName, description, sponsorURL)
					VALUES (?, ?, ?, ?, ?)
				", $sponsorUID, $groupUID, $sponsors[$i]["sponsorName"], $sponsors[$i]["description"], $sponsors[$i]["sponsorURL"]);
			}
			
			// save the group modules
			for($i = 0; $i < sizeof($modules); $i++) {
				
				$queries[] = Database::buildQuery("
					INSERT INTO GroupModules (groupUID, moduleUID)
					VALUES (?, ?)
				", $groupUID, $modules[$i]);
			}
			
			// execute the transaction
			$this->conn->transaction($queries);
			
			return $groupUID;
		}
		
		/**
		 * Edits the details of a group.
		 * @param $sessionID The sessionID of the user calling this function.
		 * @param $groupUID The group to edit.
		 * @param $groupName The group name of the group.
		 * @param $description A description of the group.
		 * @param $logoURL The path to the group's logo.
		 * @param $chatURL The group's AFCS chat room url.
		 * @param $sponsors The sponsors of the group.
		 * @param $modules The modules the group uses.
		 * @return The groupUID of the edited group.
		 */
		function editGroup(
			$sessionID,
			$groupUID,
			$groupName,
			$description,
			$logoURL,
			$chatURL,
			$sponsors,
			$modules) {
			
			$this->validateSession($sessionID);
			
			// update the group details
			$query = Database::buildQuery("
				UPDATE Groups
				SET
					groupName = ?,
					description = ?,
					logoURL = ?,
					chatURL = ?
				WHERE groupUID = ?
			", $groupName, $description, $logoURL, $chatURL, $groupUID);
			$this->conn->execute($query);
			
			// remove the old group sponsors
			$query = Database::buildQuery("
				DELETE FROM GroupSponsors
				WHERE groupUID = ?
			", $groupUID);
			$this->conn->execute($query);
			
			// save the group sponsors
			for($i = 0; $i < sizeof($sponsors); $i++) {
				
				$sponsorUID = $this->conn->generateUID();
				$query = Database::buildQuery("
					INSERT INTO GroupSponsors (sponsorUID, groupUID, sponsorName, description, sponsorURL)
					VALUES (?, ?, ?, ?, ?)
				", $sponsorUID, $groupUID, $sponsors[$i]["sponsorName"], $sponsors[$i]["description"], $sponsors[$i]["sponsorURL"]);
				$this->conn->execute($query);
			}
			
			// remove the old group modules
			$query = Database::buildQuery("
				DELETE FROM GroupModules
				WHERE groupUID = ?
			", $groupUID);
			$this->conn->execute($query);
			
			// save the group modules
			for($i = 0; $i < sizeof($modules); $i++) {
				
				$query = Database::buildQuery("
					INSERT INTO GroupModules (groupUID, moduleUID)
					VALUES (?, ?)
				", $groupUID, $modules[$i]);
				$this->conn->execute($query);
			}
			
			return $groupUID;
		}
		
		/**
		 * Archives a system group.
		 * @param $sessionID The sessionID of the user calling this function.
		 * @param $groupUID The group to archive.
		 * @return The groupUID of the deleted group.
		 */
		function deleteGroup($sessionID, $groupUID) {
			
			$this->validateSession($sessionID);
			
			// archive the group
			$query = Database::buildQuery("
				UPDATE Groups
				SET isArchived = 1
				WHERE groupUID = ?
			", $groupUID);
			$this->conn->execute($query);
			
			return $groupUID;
		}
		
		/**
		 * Finds a list of modules which a group can use.
		 * @param $sessionID The sessionID of the user calling this function.
		 * @return An array of modules.
		 */
		function getModuleList($sessionID) {
			
			$this->validateSession($sessionID);
			
			// get the modules which a group can use
			$qryModules = $this->conn->execute("
				SELECT
					moduleUID,
					moduleName,
					moduleURL
				FROM Modules
			");
			
			$modules = array();
			while($recModules = mysql_fetch_assoc($qryModules)) {
				$modules[] = ModuleVO::mapObject($recModules);
			}
			
			return $modules;
		}
		
		/**
		 * Gets the details of a user.
		 * @param $sessionID The sessionID of the user calling this function.
		 * @param $userUID The user who's details to get.
		 * @return An object containing the user details.
		 */
		function getUserDetails($sessionID, $userUID) {
			
			$this->validateSession($sessionID);
			
			// get the details of a user
			$query = Database::buildQuery("
				SELECT
					userUID,
					firstName,
					lastName,
					email,
					phoneNumber,
					isAdministrator,
					description,
					gender,
					DATE_FORMAT(dateOfBirth, '%Y/%m/%d') AS dateOfBirth,
					location,
					occupation,
					institution
				FROM Users
				WHERE userUID = ?
			", $userUID);
			$qryUser = $this->conn->execute($query);
			
			$recUser = mysql_fetch_assoc($qryUser);
			return UserVO::mapObject($recUser);
		}
		
		/**
		 * Gets the details of a group.
		 * @param $sessionID The sessionID of the user calling this function.
		 * @param $groupUID The user who's details to get.
		 * @return An object containing the group details.
		 */
		function getGroupDetails($sessionID, $groupUID) {
			
			$this->validateSession($sessionID);
			
			$result = array();
			
			// get modules linked to the group
			$query = Database::buildQuery("
				SELECT
					Modules.moduleUID,
					Modules.moduleName,
					Modules.moduleURL,
					CASE
						WHEN GroupModules.groupUID IS NULL
						THEN 0
						ELSE 1
					END AS active
				FROM Modules
				LEFT OUTER JOIN GroupModules
					ON GroupModules.moduleUID = Modules.moduleUID
				AND GroupModules.groupUID = ?
			", $groupUID);
			$qryModules = $this->conn->execute($query);
			
			$modules = array();
			while($recModules = mysql_fetch_assoc($qryModules)) {
				$modules[] = $recModules;
			}
			$result["modules"] = $modules;
			
			// get group's sponsors
			$query = Database::buildQuery("
				SELECT
					sponsorUID,
					groupUID,
					sponsorName,
					description,
					sponsorURL
				FROM GroupSponsors
				WHERE groupUID = ?
			", $groupUID);
			$qrySponsors = $this->conn->execute($query);
			
			$sponsors = array();
			while($recSponsors = mysql_fetch_assoc($qrySponsors)) {
				$sponsors[] = SponsorVO::mapObject($recSponsors);
			}
			$result["sponsors"] = $sponsors;
			
			return $result;
		}
		
		/**
		 * Activates a group's chat room.
		 * @param $sessionID The sessionID of the user calling this function.
		 * @param $groupUID The group who's chat to activate.
		 * @param $chatURL The chat room url of the group.
		 */
		function activateChatRoom($sessionID, $groupUID, $chatURL) {
			
			$this->validateSession($sessionID);
			
			// activate the group's chat room
			$query = Database::buildQuery("
				UPDATE Groups
				SET chatURL = ?
				WHERE groupUID = ?
			", $chatURL, $groupUID);
			$this->conn->execute($query);
		}
	}
?>